

import time, sys, os
import MySQLdb, MySQLdb.cursors, _mysql_exceptions
import config
import DBUtils.PooledDB


pool = DBUtils.PooledDB.PooledDB(creator=MySQLdb,
        mincached=2,
        maxcached=10,
        maxconnections=20,
        host=config.HOST,
        user=config.USER,
        passwd=config.PASSWD,
        db=config.DB,
        charset='utf8',
        cursorclass=MySQLdb.cursors.DictCursor)

def get_max_id(table):

    check_table(table)

    sql = """select max(id) as max_id from flash_""" + table

    db = pool.connection()

    cur = db.cursor()
    cur.execute(sql)
    res = cur.fetchone()
    print res['max_id']
    cur.close()

    db.close()

    return res['max_id']

def get_refresh_list(table):
    
    check_table(table)

    sql = """select `id` from flash_""" + table + """ where refresh = '1' """

    db = pool.connection()

    cur = db.cursor()
    cur.execute(sql)
    res = cur.fetchall()
    cur.close()

    db.close()

    return res


def need_refresh(table, id):
    """ return True on should refresh row
    """
    
    check_table(table)

    sql = """select `refresh` from flash_""" + table + """ where id = %s """

    db = pool.connection()

    cur = db.cursor()
    cur.execute(sql, (id,))
    res = cur.fetchone()
    if not res:
        return True
    cur.close()

    db.close()

    return res['refresh'] == 1

def set_refreshed(table, id):
    
    check_table(table)

    sql = """update flash_""" + table + """ set refresh = '0' where id = %s """

    db = pool.connection()

    cur = db.cursor()
    cur.execute(sql, (id,))
    cur.close()

    db.commit()
    db.close()


def insert_row(table, data):

    check_table(table)

    if not data.get('id'):
        raise Exception('empty id')

    sql = """insert into flash_""" + table + """ 
    (`id`,`name_cn`,`category`,`tag`,`game_help`,`last_crawl_time`)  
    values 
    (%s, %s, %s, %s, %s, UNIX_TIMESTAMP()) 
    on duplicate key update
    name_cn = %s,
    category = %s,
    tag = %s,
    game_help = %s,
    last_crawl_time = UNIX_TIMESTAMP()
    """

    params = (
            data.get('id'), 
            data.get('name'), 
            data.get('category'), 
            data.get('tag'), 
            data.get('help'),
            data.get('name'), 
            data.get('category'), 
            data.get('tag'), 
            data.get('help'),
            )

    db = pool.connection()

    cur = db.cursor()
    cur.execute(sql, params)
    cur.close()

    db.commit()

    db.close()

    set_refreshed(table, data.get('id'))

def check_table(table):
    if table not in ('4399', '7k7k'):
        raise Exception('bad table', table)

if __name__ == '__main__':

    table = '4399'

    try:
        get_max_id(table)
    except _mysql_exceptions.OperationalError, (error, message):
        print 'db error (%s, %s)' % (error, message)


    id = 12345
    if len(sys.argv) > 1:
        id = int(sys.argv[1])

    import sites.flash4399
    data = sites.flash4399.get_by_id(id)
    insert_row(table, data)



   

